package com.augurit.tool.utils;

import lombok.Value;

public class PostgresqlDiarectMetaSql extends AbstractDiarectMetaSql {
    @Override
    public String getTableNameBaseSql(String dbName, String tableName) {
//        String sql="select TC.TABLE_NAME, CASE WHEN TC.TABLE_TYPE='TABLE' THEN 'BASE TABLE' ELSE 'VIEW' END AS TABLE_TYPE,\n" +
//                "TC.COMMENTS  AS  TABLE_COMMENT,T.TABLESPACE_NAME from user_tab_comments  TC INNER JOIN\n" +
//                "USER_TABLES T ON TC.TABLE_NAME=T.TABLE_NAME AND T.TABLESPACE_NAME='USERS' AND TC.TABLE_NAME='"+tableName+"'";
        String sql="select c.relname as TABLE_NAME,\n" +
                " cast (\n" +
                " obj_description (c.relfilenode, 'pg_class') as varchar\n" +
                " ) as TABLE_COMMENT from pg_class c where c.relname = '"+tableName+"'";
        return sql;
    }

    @Override
    public String getTableColumnsBaseSql(String dbName, String tableName) {
        String sql="select\n" +
                " ordinal_position as Colorder,\n" +
                " column_name as COLUMN_NAME,\n" +
                " data_type as DATA_TYPE,\n" +
                " coalesce(character_maximum_length, numeric_precision,-1) as CHARACTER_MAXIMUM_LENGTH,\n" +
                " numeric_scale as NUMERIC_SCALE,\n" +
                " is_nullable as IS_NULLABLE,\n" +
                " column_default as DefaultVal,\n" +
                " case\n" +
                " when position('nextval' in column_default)>0 then 1\n" +
                " else 0\n" +
                " end as IsIdentity,\n" +
                " case\n" +
                " when b.pk_name is null then ''\n" +
                " else 'PRI'\n" +
                " end as COLUMN_KEY,\n" +
                " c.DeText as COLUMN_COMMENT\n" +
                " from\n" +
                " information_schema.columns\n" +
                " left join (\n" +
                " select\n" +
                " pg_attr.attname as colname,\n" +
                " pg_constraint.conname as pk_name\n" +
                " from\n" +
                " pg_constraint\n" +
                " inner join pg_class on\n" +
                " pg_constraint.conrelid = pg_class.oid\n" +
                " inner join pg_attribute pg_attr on\n" +
                " pg_attr.attrelid = pg_class.oid\n" +
                " and pg_attr.attnum = pg_constraint.conkey[1]\n" +
                " inner join pg_type on\n" +
                " pg_type.oid = pg_attr.atttypid\n" +
                " where\n" +
                " pg_class.relname = '"+tableName.toLowerCase()+"'\n" +
                " and pg_constraint.contype = 'p' \n" +
                " ) b on\n" +
                " b.colname = information_schema.columns.column_name\n" +
                " left join (\n" +
                " select\n" +
                " attname,\n" +
                " description as DeText\n" +
                " from\n" +
                " pg_class\n" +
                " left join pg_attribute pg_attr on\n" +
                " pg_attr.attrelid = pg_class.oid\n" +
                " left join pg_description pg_desc on\n" +
                " pg_desc.objoid = pg_attr.attrelid\n" +
                " and pg_desc.objsubid = pg_attr.attnum\n" +
                " where\n" +
                " pg_attr.attnum>0\n" +
                " and pg_attr.attrelid = pg_class.oid\n" +
                " and pg_class.relname = '"+tableName.toLowerCase()+"'\n" +
                ")c on\n" +
                " c.attname = information_schema.columns.column_name\n" +
                " where\n" +
                " table_schema = '"+dbName+"'\n" +
                " and table_name = '"+tableName.toLowerCase()+"'\n" +
                " order by\n" +
                " ordinal_position asc" +
                "  ";
        return sql;
    }
    @Override
    public String getTableNameDesc(String dbName) {
        String sql="";
        if(dbName==null){
            sql= "select TC.TABLE_NAME, CASE WHEN TC.TABLE_TYPE='TABLE' THEN 'BASE TABLE' ELSE 'VIEW' END AS TABLE_TYPE,\n" +
                    "TC.COMMENTS  AS  TABLE_COMMENT,T.TABLESPACE_NAME from user_tab_comments  TC INNER JOIN\n" +
                    "USER_TABLES T ON TC.TABLE_NAME=T.TABLE_NAME AND T.TABLESPACE_NAME='USERS' AND TC.TABLE_NAME='?'";
        }
        return sql;
    }
}
